' VBScript source code


msgbox "starting script"

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")

'procs section
folder = "C:\Users\dwentzel\Desktop\MD3\0400Tables"



Set f = fso.GetFolder(folder)
Set fc = f.Files


'now fix each file
For Each f1 in fc
CurrentFile = f1.name
CurrentExtension = Right(CurrentFile,3)

		If UCASE(CurrentExtension) = "SQL" Then
		
			'msgbox CurrentFile

			'find the object name
			ObjName = mid(CurrentFile,1,instr(5,CurrentFile,".")-1)
			'msgbox ObjName
			
			FileToOpen = folder & "\" & CurrentFile
			
			'determine number of lines in file
			Set FileToFix = fso.OpenTextFile(FileToOpen, ForReading)
			iLineCounter = 0 
			
			do while FileToFix.AtEndofStream <> True
			    LineContents = FileToFix.ReadLine
			    iLineCounter = iLineCounter + 1
			loop
			
			FileToFix.Close
			Set FileToFix = Nothing
			Linecontents = ""
			
			
			
			'new file
			NewFileName = replace(CurrentFile,".Table","")
			FileToCreate = folder & "\" & NewFileName
			Set NewFile = fso.CreateTextFile(FileToCreate,ForWriting)
			
			'add some stuff to the top
			NewFile.WriteLine "---------------------------------------------------------------------- "
			NewFile.WriteLine "--Whenever adding a column to a table ensure it is added to the CREATE "
			NewFile.WriteLine "--and ALTER sections (for net new vs upgraded databases) "
			NewFile.WriteLine "---------------------------------------------------------------------- "
			
			'Open the file again, read it, begin sending output to a new file, but dont output the last two lines
			iNewFileLineCounter = 0 
			Set FileToFix = fso.OpenTextFile(FileToOpen, ForReading)
			do while (FileToFix.AtEndofStream <> True) AND (iNewFileLineCounter <= iLineCounter -2)
			    iNewFileLineCounter = iNewFileLineCounter + 1
			    LineContents = ""
			    LineContents = FileToFix.ReadLine
			    NewFile.WriteLine (LineContents)
			loop
			
			FileToFix.Close
			Set FileToFix = Nothing
			
			'now add the new "alter text"
			NewFile.WriteBlankLines(1)
			NewFile.WriteLine "---------------------------------------------------------------------- "
			NewFile.WriteLine "-- Alter Statements begin here "
			NewFile.WriteLine "-- All SQL MUST be rerunnable  "
			NewFile.WriteLine "---------------------------------------------------------------------- "
			NewFile.WriteLine "ELSE BEGIN "
			NewFile.WriteBlankLines(1)
			strLine = "     Print 'Table " & ObjName & " already exists, performing Alters (if required)...'"
			NewFile.WriteLine strLine
			NewFile.WriteBlankLines(1)
			NewFile.WriteLine "END --ALTERS  "
			NewFile.WriteLine "GO  "
			NewFile.WriteBlankLines(1)

			NewFile.Close
			Set NewFile = Nothing

		end if
Next 
set fc = nothing
set f = nothing

'now look for all files with .Table in their name...delete them.  
Set f = fso.GetFolder(folder)
Set fc = f.Files


'now fix each file
For Each f1 in fc
    CurrentFile = f1.name
    If instr(1,CurrentFile,".Table.") <> 0 Then
        FileToDelete = folder & "\" & CurrentFile
        set MyFile = fso.GetFile(FileToDelete)
        MyFile.Delete
        Set MyFile = Nothing
    End if
Next

set fc = nothing
set f = nothing
msgbox "done"
		
		
		
		
